
package com.dstz.form.util;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dstz.base.api.response.impl.ResultMsg;
import com.dstz.base.core.util.BeanUtils;
import com.dstz.base.core.util.ExceptionUtil;
import com.dstz.base.core.util.StringUtil;
import com.dstz.form.constants.SqlConstants;

import cn.hutool.core.exceptions.UtilException;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.AllColumns;
import net.sf.jsqlparser.statement.select.AllTableColumns;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.Limit;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.util.AddAliasesVisitor;

/**
 * Created by 张忠亮 on 2015/6/8.
 */
public class SqlUtil {
	
	protected final static Logger log = LoggerFactory.getLogger(SqlUtil.class);
	
	public static final String DATABSE_TYPE_MYSQL = "mysql";
	
	public static final String DATABSE_TYPE_POSTGRE = "postgresql";
	
	public static final String DATABSE_TYPE_ORACLE = "oracle";
	
	public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";
	
	/**
	 * 分页SQL
	 */
	public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}"; // mysql
	
	public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";// postgresql
	
	public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}"; // oracle
	
	public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}"; // sqlserver
	
	/**
	 * 获取所有表的SQL
	 */
	public static final String MYSQL_ALLTABLES_SQL = "select distinct table_name from information_schema.columns where table_schema = {0}";
	
	public static final String POSTGRE__ALLTABLES_SQL = "SELECT distinct c.relname AS  table_name FROM pg_class c";
	
	public static final String ORACLE__ALLTABLES_SQL = "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
	
	public static final String SQLSERVER__ALLTABLES_SQL = "select distinct c.name as  table_name from sys.objects c";
	
	/**
	 * 获取指定表的所有列名
	 */
	public static final String MYSQL_ALLCOLUMNS_SQL = "select column_name from information_schema.columns where table_name = {0} and table_schema = {1}";
	
	public static final String POSTGRE_ALLCOLUMNS_SQL = "select table_name from information_schema.columns where table_name = {0}";
	
	public static final String ORACLE_ALLCOLUMNS_SQL = "select column_name from all_tab_columns where table_name ={0}";
	
	public static final String SQLSERVER_ALLCOLUMNS_SQL = "select name from syscolumns where id={0}";
	
	/**
	 * 获取全sql
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static String getFullSql(String sql, Map params) {
		StringBuilder sqlB = new StringBuilder();
		sqlB.append("SELECT t.* FROM ( ");
		sqlB.append(sql + " ");
		sqlB.append(") t ");
		if (params != null && params.size() >= 1) {
			sqlB.append("WHERE 1=1  ");
			Iterator it = params.keySet().iterator();
			while (it.hasNext()) {
				String key = String.valueOf(it.next());
				String value = String.valueOf(params.get(key));
				if (!StringUtil.isEmpty(value) && !"null".equals(value)) {
					sqlB.append(" AND ");
					sqlB.append(" " + key + value);
				}
			}
		}
		return sqlB.toString();
	}
	
	/**
	 * 获取求数量sql
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static String getCountSql(String sql, Map params) {
		String querySql = getFullSql(sql, params);
		querySql = "SELECT COUNT(*) FROM (" + querySql + ") t2";
		return querySql;
	}
	
	/**
	 * 获取列求和sql
	 * @param sql
	 * @param fields
	 * @return
	 */
	public static String getAddupSql(String sql, String[] fields) {
		String sumFields = "";
		for (int i = 0; i < fields.length; i++) {
			if (i < fields.length - 1) {
				sumFields += " sum(" + fields[i].trim() + ") as " + fields[i].trim() + ",";
			} else {
				sumFields += " sum(" + fields[i].trim() + ") as " + fields[i].trim() + " ";
			}
		}
		String addupSql = "SELECT " + sumFields + " FROM (" + sql + ") t2";
		return addupSql;
	}
	
	/**
	 * 获取sql查询的总数
	 * 
	 * @param sql
	 * @return
	 * @return String
	 * @author xianggang
	 * @date 2016年9月8日 下午2:57:54
	 **/
	public static String getCountSql(String sql) {
		String result = "";
		try {
			
			Select select = (Select) CCJSqlParserUtil.parse(sql);
			
			PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
			List<SelectItem> selectItems = plainSelect.getSelectItems();
			if (CollectionUtils.isEmpty(selectItems)) {
				return SqlUtil.getSimpleCountSql(sql);
			}
			// 去掉limit
			Limit limit = plainSelect.getLimit();
			if (limit != null) {
				plainSelect.setLimit(null);
			}
			List<String> fieldList = new ArrayList<String>();
			Iterator<SelectItem> iterator = selectItems.iterator();
			boolean hasColumn = false;
			while (iterator.hasNext()) {
				SelectItem next = iterator.next();
				if (next instanceof AllColumns) {
					continue;
				}
				SelectExpressionItem selectItem = (SelectExpressionItem) next;
				Expression expression = selectItem.getExpression();
				String field = "";
				Alias alias = selectItem.getAlias();
				if ((expression instanceof SubSelect)) {
					hasColumn = true;
					field = alias.getName().replaceAll("`", "");
					fieldList.add(field);
					continue;
				}
				if (!hasColumn) {
					hasColumn = true;
					if (alias != null) {
						field = alias.getName().replaceAll("`", "");
					} else {
						field = ((Column) expression).getColumnName();
					}
					fieldList.add(field);
					continue;
				}
			}
			
			// 去掉orderby
			plainSelect.setOrderByElements(new ArrayList<OrderByElement>());
			// 去掉group by 后来发现不能去掉group by
			// plainSelect.setGroupByColumnReferences( null );
			
			if (fieldList.size() == 0) {
				
				// 判断是否有group by 如果有,则需要包装一层
				
				plainSelect.setSelectItems(getCountItem()); // 替换查询字段为 count(*)
				sql = select.toString();
				if (hasGroupByExp(sql)) {
					sql = "select count(1) as counts from (" + sql + ") B";
				}
				return sql;
			}
			
			// 获取内层的查询sql
			String outWhere = getNoOutWhereSql(select, ArrayUtil.toStringArray(fieldList));
			
			String countSql = "";
			if (StringUtils.isEmpty(outWhere)) {
				plainSelect.setSelectItems(getCountItem()); // 替换查询字段为 count(*)
				countSql = select.toString();
				
				if (hasGroupByExp(countSql)) {
					countSql = "select count(1) as counts from (" + countSql + ") B";
				}
			} else {
				sql = select.toString();
				countSql = "select count(1) as counts from (" + sql + ") B where 1=1  " + outWhere;
			}
			
			return countSql;
		} catch (JSQLParserException e) {
			log.error("获取Count SQL时出现异常, sql:{} , e: {}", sql, e);
			result = SqlUtil.getSimpleCountSql(sql);
		}
		return result;
	}
	
	/**
	 * 升级sql查询引擎 [暂只支持mysql]
	 * 
	 * @param realSqlColumn 真实的查询字段
	 * @param sql
	 * @param paramData 页面请求参数
	 * @param columnTableMap 字段对应的名称
	 * @param page 起始页
	 * @param rows 每页大小
	 * @return
	 * @return String
	 * @author xianggang
	 * @param pageSearchFields
	 * @date 2018年7月25日 下午2:14:16
	 */
	public static String sqlReportUpgradeSql(String realSqlColumn, String sql, Map<String, Object> paramData,
	        Map<String, String> columnTableMap, int page, int rows) {
		
		// 获取group by
		List<Expression> groupByExp = getGroupByExps(sql);
		
		// 清除group by
		if (!CollectionUtils.isEmpty(groupByExp)) {
			sql = removeGroupByExp(sql);
		}
		
		// 获取order by
		List<OrderByElement> orderByExp = getOrderByExp(sql);
		
		// 移除orderby
		if (!CollectionUtils.isEmpty(orderByExp)) {
			sql = removeOrderByExp(sql);
		}
		
		// 添加上where条件
		sql = addWhere(sql, paramData, columnTableMap);
		
		// 加回group by
		sql = addGroupByExp(sql, groupByExp);
		
		// 加回order by
		sql = addOrderByExp(sql, orderByExp);
		
		sql = getQueryDataSql(realSqlColumn, sql, page, rows);
		
		return sql;
	}
	
	public static String getQueryDataSql(String realSqlColumn, String sql, int curPage, int pageSize) {
		
		// 分页
		int offset = (curPage - 1) * pageSize;
		
		String limit = " limit " + offset + "," + pageSize;
		
		String dataSql = sql + limit;
		String[] realSqlColumnArr = realSqlColumn.split(", "); // 注意这个空格不能丢掉
		dataSql = SqlUtil.optimizeSql(dataSql, realSqlColumnArr);
		
		return dataSql;
	}
	
	// 加回group by
	public static String addGroupByExp(String sql, List<Expression> groupByExp) {
		try {
			if (groupByExp == null) {
				return sql;
			}
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				plainSelect.setGroupByColumnReferences(groupByExp);
				return selectStatement.toString();
			}
		} catch (JSQLParserException e) {
			log.error("加回group by异常 : sql : {} ,e:{}", sql, e);
			return sql;
		}
		
		return sql;
	}
	
	/**
	 * 移除group by
	 * 
	 * @param sql
	 * @return
	 * @return String
	 * @author as
	 * @date 2018年7月30日 下午2:46:58
	 */
	public static String removeGroupByExp(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				List<Expression> orderByElements = plainSelect.getGroupByColumnReferences();
				if (orderByElements != null) {
					plainSelect.setGroupByColumnReferences(null);
					return selectStatement.toString();
				}
				return sql;
			}
		} catch (JSQLParserException e) {
			log.error("移除group by异常 : sql : {} ,e:{}", sql, e);
			return sql;
		}
		
		return sql;
	}
	
	private static String addWhere(String sql, Map<String, Object> paramData, Map<String, String> columnTableMap) {
		// 加上查询条件
		boolean hasWhereExp = hasWhereExp(sql);
		if (paramData != null) {
			if (!hasWhereExp) {
				sql += " where 1=1 ";
			}
			Set<String> keySet = paramData.keySet();
			for (String key : keySet) {
				
				String condition = SqlConstants.OP_EQ;
				
				Object value = paramData.get(key);
				if (!StringUtil.isNotEmpty(value)) {
					continue;
				}
				String column = columnTableMap.get(key);
				if (StringUtil.isEmpty(column)) {
					if (key.endsWith("_begin")) {
						key = key.replace("_begin", "");
						condition = SqlConstants.OP_RQ;
					} else if (key.endsWith("_end")) {
						key = key.replace("_end", "");
						condition = SqlConstants.OP_LQ;
					} else if (key.startsWith("like_")) {
						key = key.replace("like_", "");
						condition = SqlConstants.OP_LIKE;
					} else if (key.startsWith("in_")) {
						key = key.replace("in_", "");
						condition = SqlConstants.OP_IN;
					} else {
						log.info("找不到字段" + key);
						continue;
					}
					column = columnTableMap.get(key);
				}
				
				// 防止sql 注入
				SqlInjectionUtil.filterContent(value.toString());
				
				if (StringUtils.equals(condition, SqlConstants.OP_LIKE)) {
					//and l.字段名   like "%值%"
					sql += " AND " + column + condition + " '%" + value + "%'";
				} else if (StringUtils.equals(condition, SqlConstants.OP_IN)) {
					value = ArrayUtil.join(value.toString().split(","), "','");
					sql += " AND " + column + condition + " ('" + value + "')";
				} else {
					sql += " AND " + column + condition + " '" + value + "'";
				}
				
			}
		}
		return sql;
	}
	
	private static int getAfterSelectInsertPoint(String sql) {
		int selectIndex = sql.toLowerCase().indexOf("select");
		int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
		return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
	}
	
	public static String getAllTableSql(String dbType, String... param) {
		if (StringUtil.isNotEmpty(dbType)) {
			if (dbType.equals(DATABSE_TYPE_MYSQL)) {
				return MessageFormat.format(MYSQL_ALLTABLES_SQL, param);
			} else if (dbType.equals(DATABSE_TYPE_ORACLE)) {
				return ORACLE__ALLTABLES_SQL;
			} else if (dbType.equals(DATABSE_TYPE_POSTGRE)) {
				return POSTGRE__ALLTABLES_SQL;
			} else if (dbType.equals(DATABSE_TYPE_SQLSERVER)) {
				return SQLSERVER__ALLTABLES_SQL;
			}
		}
		return null;
	}
	
	public static String getAllCloumnSql(String dbType, String... param) {
		if (StringUtil.isNotEmpty(dbType)) {
			if (dbType.equals(DATABSE_TYPE_MYSQL)) {
				return MessageFormat.format(MYSQL_ALLCOLUMNS_SQL, param);
			} else if (dbType.equals(DATABSE_TYPE_ORACLE)) {
				return MessageFormat.format(ORACLE_ALLCOLUMNS_SQL, param);
			} else if (dbType.equals(DATABSE_TYPE_POSTGRE)) {
				return MessageFormat.format(POSTGRE_ALLCOLUMNS_SQL, param);
			} else if (dbType.equals(DATABSE_TYPE_SQLSERVER)) {
				return MessageFormat.format(SQLSERVER_ALLCOLUMNS_SQL, param);
			}
		}
		return null;
	}
	
	/**
	 * 判断是否是查询的sql
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date 2016年9月22日 下午9:48:35
	 **/
	public static ResultMsg<Statement> isSelectSql(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			if (stmt instanceof Select) {
				return ResultMsg.SUCCESS(stmt);
			}
			return ResultMsg.ERROR("该脚本不是SQL查询语句");
		} catch (Exception e) {
			String errorMsg = ExceptionUtil.getExceptionMessage(e);
			errorMsg = StringUtil.truncateStr(errorMsg, 500);
			return ResultMsg.ERROR(errorMsg);
		}
	}
	
	/**
	 * 获取绝不查询出数据的sql
	 * 
	 * @param sql
	 * @return
	 * @return String
	 * @author xianggang
	 * @date: 2017年9月1日 下午8:25:17
	 */
	public static String getNoDataSql(String sql) {
		try {
			Select select = (Select) CCJSqlParserUtil.parse(sql);
			PlainSelect selectBody = (PlainSelect) select.getSelectBody();
			Expression where = selectBody.getWhere();
			if (where == null) {
				EqualsTo equalsTo = new EqualsTo();
				equalsTo.setLeftExpression(new Column("1"));
				equalsTo.setRightExpression(new LongValue("1"));
				where = equalsTo;
			} else {
				EqualsTo equalsTo = new EqualsTo();
				equalsTo.setLeftExpression(new Column("1"));
				equalsTo.setRightExpression(new LongValue("2"));
				where = new AndExpression(where, equalsTo);
			}
			selectBody.setWhere(where);
			
			return select.toString();
		} catch (Exception e) {
			log.error("sql解析异常，sql:{}, e:{}", sql, e);
			return null;
		}
	}
	
	/**
	 * 获取绝不查询出数据的sql
	 * 
	 * @param sql
	 * @return
	 * @return String
	 * @author xianggang
	 * @date: 2017年9月1日 下午8:25:17
	 */
	public static String removeDataSql(String sql) {
		try {
			Select select = (Select) CCJSqlParserUtil.parse(sql.trim());
			PlainSelect selectBody = (PlainSelect) select.getSelectBody();
			Expression where = selectBody.getWhere();
			if (where == null) {
				EqualsTo equalsTo = new EqualsTo();
				equalsTo.setLeftExpression(new Column("1"));
				equalsTo.setRightExpression(new LongValue("1"));
				where = equalsTo;
			} else {
				EqualsTo equalsTo = new EqualsTo();
				equalsTo.setLeftExpression(new Column("1"));
				equalsTo.setRightExpression(new LongValue("2"));
				where = new AndExpression(where, equalsTo);
			}
			selectBody.setWhere(where);
			
			return select.toString();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("sql解析异常，sql: {}, e:{}", sql, e.getMessage(), e);
			return null;
		}
	}
	
	public static JSONObject getColumnTableRefJo(String sql) throws JSQLParserException {
		
		JSONArray columnTableRefJa = getColumnTableRefJa(sql);
		JSONObject jo = new JSONObject();
		for (Object object : columnTableRefJa) {
			String string = object.toString();
			String column = JSONObjectUtil.getValByKey("column", string);
			jo.put(column.toLowerCase(), object);
		}
		return jo;
	}
	
	/**
	 * 获取
	 * 
	 * @param sql
	 * @return
	 * @throws JSQLParserException
	 * @return JSONArray
	 * @author xianggang
	 * @date: 2017年4月7日 下午6:05:24
	 */
	public static JSONArray getColumnTableRefJa(String sql) throws JSQLParserException {
		Statement stmt = CCJSqlParserUtil.parse(sql);
		Select selectStatement = (Select) stmt;
		SelectBody selectBody = selectStatement.getSelectBody();
		JSONArray columnTableJa = new JSONArray();
		if (selectBody instanceof PlainSelect) {
			// 普通的查询sql
			PlainSelect plainSelect = (PlainSelect) selectBody;
			List<SelectItem> selectItems = plainSelect.getSelectItems();
			Map<String, String> tableMap = new HashMap<>();
			String defaultTableName = "";
			String defaultTableAlias = "";
			if (plainSelect.getFromItem() instanceof Table) {
				Table fromItem = (Table) plainSelect.getFromItem();
				defaultTableName = fromItem.getName();
				Alias alias = fromItem.getAlias();
				if (!BeanUtils.isEmpty(alias)) {
					defaultTableAlias = alias.getName();
					tableMap.put(alias.getName(), fromItem.getName());
				}
				JSONObject jo = new JSONObject();
				jo.put("column", "default");
				jo.put("tableAlias", defaultTableAlias);
				jo.put("tableName", defaultTableName);
				columnTableJa.add(jo);
			}
			
			List<Join> joins = plainSelect.getJoins();
			if (!CollectionUtils.isEmpty(joins)) {
				for (Join join : joins) {
					if (join.getRightItem() instanceof Table) {
						Table rightItem = (Table) join.getRightItem();
						tableMap.put(rightItem.getAlias().getName(), rightItem.getName());
					}
				}
			}
			
			for (SelectItem selectItem : selectItems) {
				if (selectItem instanceof AllColumns || selectItem instanceof AllTableColumns) {
					continue;
				}
				SelectExpressionItem item = (SelectExpressionItem) selectItem;
				Alias fieldAlias = item.getAlias();
				String expression = item.getExpression().toString();
				expression = expression.replaceAll("`", "");
				String[] split = expression.split("\\.");
				String column = "";
				String tableAlias = "";
				String tableName = "";
				if (split.length == 1) {
					column = split[0];
					tableAlias = defaultTableAlias;
					tableName = defaultTableName;
				} else {
					tableAlias = split[0];
					column = split[1];
					tableName = tableMap.get(tableAlias);
				}
				
				if (BeanUtils.isNotEmpty(fieldAlias)) {
					column = fieldAlias.getName() + "." + column;
				} else {
					column = column + "." + column;
				}
				column = column.replaceAll("`", "");
				
				JSONObject jo = new JSONObject();
				jo.put("column", column);
				jo.put("tableAlias", tableAlias);
				if (StringUtils.isEmpty(tableName)) {
					// throw new RuntimeException( "sql格式错误" );
				}
				jo.put("tableName", tableName);
				columnTableJa.add(jo);
			}
		}
		return columnTableJa;
	}
	
	/**
	 * 判断sql是否有where条件
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 **/
	public static boolean hasWhereExp(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				return !BeanUtils.isEmpty(plainSelect.getWhere());
			}
		} catch (JSQLParserException e) {
			log.error("判断sql是否有where条件 的sql语句有问题 : sql : {} ,e:{}", sql, e);
			return false;
		}
		
		return false;
	}
	
	/**
	 * 去掉order by
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 **/
	public static List<OrderByElement> getOrderByExp(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
				if (orderByElements != null) {
					return orderByElements;
				}
			}
		} catch (JSQLParserException e) {
			log.error("去掉order by异常 : sql : {} ,e:{}", sql, e);
			return new ArrayList<>();
		}
		
		return new ArrayList<>();
	}
	
	/**
	 * 去掉order by
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 **/
	public static String removeOrderByExp(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				List<OrderByElement> orderByElements = plainSelect.getOrderByElements();
				if (orderByElements != null) {
					plainSelect.setOrderByElements(new ArrayList<OrderByElement>());
					return selectStatement.toString();
				}
				return sql;
			}
		} catch (JSQLParserException e) {
			log.error("去掉order by异常 : sql : {} ,e:{}", sql, e);
			return sql;
		}
		
		return sql;
	}
	
	/**
	 * 加回order by
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 **/
	public static String addOrderByExp(String sql, List<OrderByElement> orderByElements) {
		try {
			if (orderByElements == null) {
				return sql;
			}
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				plainSelect.setOrderByElements(orderByElements);
				return selectStatement.toString();
			}
		} catch (JSQLParserException e) {
			log.error("加回order by异常 : sql : {} ,e:{}", sql, e);
			return sql;
		}
		
		return sql;
	}
	
	/**
	 * 判断sql是否有where条件
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date: 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 */
	public static boolean hasGroupByExp(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				
				return !BeanUtils.isEmpty(plainSelect.getGroupByColumnReferences());
			}
		} catch (JSQLParserException e) {
			log.error("判断sql是否有group by 条件 的sql语句有问题 : sql : {} ,e:{}", sql, e);
			return false;
		}
		
		return false;
	}
	
	/**
	 * 判断sql是否有where条件
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date: 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 */
	public static String getGroupByExp(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				
				List<Expression> groupByColumnReferences = plainSelect.getGroupByColumnReferences();
				if (!CollectionUtils.isEmpty(groupByColumnReferences)) {
					String[] grp = new String[groupByColumnReferences.size()];
					for (int i = 0; i < groupByColumnReferences.size(); i++) {
						grp[i] = groupByColumnReferences.get(i).toString();
					}
					String grpExp = org.apache.commons.lang3.StringUtils.join(grp, " ");
					
					return " group by " + grpExp.toLowerCase();
					
				}
				return "";
			}
		} catch (JSQLParserException e) {
			log.error("判断sql是否有group by 条件 的sql语句有问题 : sql : {} ,e:{}", sql, e);
			return "";
		}
		
		return "";
	}
	
	/**
	 * 获取groupby的表达式
	 * 
	 * @param sql
	 * @return
	 * @return boolean
	 * @author xianggang
	 * @date: 2016年11月19日 下午1:52:16
	 * @throws JSQLParserException
	 */
	public static List<Expression> getGroupByExps(String sql) {
		try {
			Statement stmt = CCJSqlParserUtil.parse(sql);
			Select selectStatement = (Select) stmt;
			SelectBody selectBody = selectStatement.getSelectBody();
			if (selectBody instanceof PlainSelect) {
				// 普通的查询sql
				PlainSelect plainSelect = (PlainSelect) selectBody;
				
				List<Expression> groupByColumnReferences = plainSelect.getGroupByColumnReferences();
				if (groupByColumnReferences != null) {
					return groupByColumnReferences;
				}
			}
		} catch (JSQLParserException e) {
			log.error("判断sql是否有group by 条件 的sql语句有问题 : sql : {} ,e:{}", sql, e);
			return new ArrayList<>();
		}
		return new ArrayList<>();
	}
	
	/**
	 * 获取主表的表名和别名
	 * 
	 * @param sql
	 * @return
	 * @throws JSQLParserException
	 * @return JSONObject
	 * @author xianggang
	 * @date: 2017年4月26日 上午10:53:17
	 */
	public static JSONObject getMainTableJo(String sql) {
		JSONObject jo = new JSONObject();
		Statement stmt = null;
		try {
			stmt = CCJSqlParserUtil.parse(sql);
		} catch (JSQLParserException e) {
			log.error(e.getMessage());
			return jo;
		}
		Select selectStatement = (Select) stmt;
		SelectBody selectBody = selectStatement.getSelectBody();
		if (selectBody instanceof PlainSelect) {
			PlainSelect plainSelect = (PlainSelect) selectBody;
			Table fromItem = (Table) plainSelect.getFromItem();
			Alias alias = fromItem.getAlias();
			jo.put("tableName", fromItem.getName());
			if (!BeanUtils.isEmpty(alias)) {
				jo.put("tableAlias", alias.getName());
			}
			
		}
		return jo;
	}
	
	private static List<SelectItem> getCountItem() {
		List<SelectItem> items = new ArrayList<SelectItem>();
		// 查询字段
		SelectExpressionItem countItem = new SelectExpressionItem();
		setFunction(countItem);
		// 字段别名
		countItem.setAlias(new Alias("counts"));
		// 添加到字段列表
		items.add(countItem);
		return items;
	}
	
	private static void setFunction(SelectExpressionItem countItem) {
		// 字段属性
		Function function = new Function();
		function.setName("COUNT");
		function.setAllColumns(true);
		countItem.setExpression(function);
	}
	
	/**
	 * 使用项目原先的解析方式
	 * 
	 * @param sql
	 * @return
	 **/
	public static String getSimpleCountSql(String sql) {
		return String.format("select count(*) counts from (%s) tmp", sql.toString());
	}
	
	/**
	 * 替换单双引号,,配合前端传输文本..
	 * 
	 * @param str
	 * @return
	 * @return String
	 * @author xianggang Date : 2016年4月9日 下午5:14:54
	 */
	public static String resetQuoteStr(String str) {
		if (StringUtils.isEmpty(str)) {
			return "";
		}
		str = str.replaceAll(QutoConstants.SINGLE_QUOTE, "'").replaceAll(QutoConstants.QUOTE, "\"")
		        .replaceAll(QutoConstants.BACKSLASH, "\\\\");
		return str;
	}
	
	/**
	 * 优化sql
	 * 1.剔除不要的字段
	 * 2.将where条件中的子查询包装在外层
	 * 
	 * @param sql
	 * @return
	 * @return String
	 * @author xianggang
	 * @date: 2017年9月1日 下午8:25:17
	 */
	public static String optimizeSql(String sql, String[] realSqlColumnArr) {
		try {
			
			if (realSqlColumnArr == null) {
				return sql;
			}
			
			Select select = (Select) CCJSqlParserUtil.parse(sql);
			
			// 获取内层的查询sql
			String outWhere = getNoOutWhereSql(select, realSqlColumnArr);
			
			if (StringUtils.isEmpty(outWhere)) {
				return select.toString();
			}
			
			PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
			Limit limit = plainSelect.getLimit();
			plainSelect.setLimit(null);
			
			sql = select.toString();
			
			// 判断有没有查询条件出外层需要的这个字段, 一般都有,所以暂时不用判断,以后如果有特殊的情况再另做考虑
			
			String realSqlColumn = StringUtils.join(realSqlColumnArr, ",");
			
			String limitStr = "";
			if (limit != null) {
				limitStr = limit.toString();
			}
			// 将where条件中的子查询条件提出来
			String newSql = "select " + realSqlColumn + " from (" + sql + ") B where 1=1 " + outWhere + " " + limitStr;
			
			return newSql;
		} catch (Exception e) {
			log.error("sql解析异常，sql:{}, e:{}", sql, e);
			throw new UtilException("剔除掉查询中不需要的字段异常,e:", ExceptionUtil.getExceptionMessage(e));
		}
	}
	
	private static String getNoOutWhereSql(Select select, String[] realSqlColumnArr) {
		
		List<String> realSqlColumnList = new ArrayList<>();
		for (String column : realSqlColumnArr) {
			
			// 如果字段中含有时间格式化参数,则需要提取出其中的字段
			if (column.indexOf("date_format(") >= 0) {
				column = column.split(" as ")[1];
			}
			column = column.trim();
			// 去掉`符号
			realSqlColumnList.add(column.replaceAll("`", ""));
			
		}
		
		PlainSelect selectBody = (PlainSelect) select.getSelectBody();
		List<SelectItem> selectItems = selectBody.getSelectItems();
		Iterator<SelectItem> iterator = selectItems.iterator();
		List<String> subSelectItem = new ArrayList<>();
		
		while (iterator.hasNext()) {
			SelectItem next = iterator.next();
			if (next instanceof AllColumns) {
				continue;
			}
			SelectExpressionItem selectItem = (SelectExpressionItem) next;
			Expression expression = selectItem.getExpression();
			if (!(expression instanceof Column)) {
				subSelectItem.add(selectItem.getAlias().getName());
			}
		}
		iterator = selectItems.iterator();
		while (iterator.hasNext()) {
			SelectItem next = iterator.next();
			if (next instanceof AllColumns) {
				continue;
			}
			SelectExpressionItem selectItem = (SelectExpressionItem) next;
			// 移除不要的字段
			removeNoNeedColumn(iterator, realSqlColumnList, selectItem);
		}
		
		// 将外层的where条件提取出来
		Expression where = selectBody.getWhere();
		Map<String, Expression> outWhereMap = new HashMap<>();
		String outWhere = removeOutWhere(where, where, false, outWhereMap, subSelectItem);
		return outWhere;
	}
	
	// 一般where的树都是从后往前迭代的,所以
	private static String removeOutWhere(Expression where, Expression parentWhere, boolean isLeft,
	        Map<String, Expression> outWhereMap, List<String> subSelectItem) {
		
		StringBuffer sb = new StringBuffer();
		if (where instanceof BinaryExpression) {
			BinaryExpression binary = (BinaryExpression) where;
			Expression leftExpression = binary.getLeftExpression();
			Expression rightExpression = binary.getRightExpression();
			if (leftExpression instanceof BinaryExpression) {
				sb.append(removeOutWhere(leftExpression, where, true, outWhereMap, subSelectItem));
			} else {
				if (leftExpression instanceof Column) {
					String whereColumn = leftExpression.toString();
					whereColumn = whereColumn.replaceAll("`", "");
					// 兼容 table别名.字段名的情况
					if (whereColumn.indexOf(".") >= 0) {
						whereColumn = whereColumn.split("\\.")[1];
					}
					if (subSelectItem.contains(whereColumn)) {
						BinaryExpression parentBinary = (BinaryExpression) parentWhere;
						Expression nullExpresssion = null;
						EqualsTo equalsTo = new EqualsTo();
						equalsTo.setLeftExpression(new Column("1"));
						equalsTo.setRightExpression(new LongValue("1"));
						nullExpresssion = equalsTo;
						if (isLeft) {
							parentBinary.setLeftExpression(nullExpresssion);
						} else {
							parentBinary.setRightExpression(nullExpresssion);
						}
						outWhereMap.put(whereColumn, where);
						((Column) binary.getLeftExpression()).setTable(null);
						if (parentWhere instanceof AndExpression) {
							sb.append(" AND " + binary.toString());
						} else {
							sb.append(" OR " + binary.toString());
						}
						
					}
				}
			}
			
			if (rightExpression instanceof BinaryExpression) {
				sb.append(removeOutWhere(rightExpression, where, false, outWhereMap, subSelectItem));
			}
		}
		return sb.toString();
		
	}
	
	// 移除不要的字段
	private static void removeNoNeedColumn(Iterator<SelectItem> iterator, List<String> realSqlColumnList,
	        SelectExpressionItem selectItem) {
		Alias alias = selectItem.getAlias();
		// 字段使用了别名
		if (alias != null) {
			String name = alias.getName().replaceAll("`", "");
			name = name.replaceAll("'", "");
			// 如果不存在,则移除
			if (!realSqlColumnList.contains(name)) {
				iterator.remove();
			}
		} else {
			// 没有使用别名
			Column column = (Column) selectItem.getExpression();
			String columnName = column.getColumnName();
			columnName = columnName.replaceAll("`", "");
			boolean isInField = false;
			for (String field : realSqlColumnList) {
				if (field.equalsIgnoreCase(columnName)) {
					isInField = true;
					break;
				}
			}
			// 如果不存在,则移除
			if (!isInField) {
				iterator.remove();
			}
		}
	}
	
	/**
	 * 给sql的字段加上别名
	 * 
	 * @param sql
	 * @return
	 * @return String
	 * @author xianggang
	 * @date: 2017年9月1日 下午8:25:17
	 */
	public static ResultMsg<String> addAliases(String sql) {
		try {
			Select select = (Select) CCJSqlParserUtil.parse(sql);
			final AddAliasesVisitor instance = new AddAliasesVisitor();
			SelectBody selectBody = select.getSelectBody();
			Map<String, String> oldColumnAliasMap = handleColumnAliasMap(selectBody, null);
			selectBody.accept(instance);
			handleColumnAliasMap(selectBody, oldColumnAliasMap);
			return ResultMsg.SUCCESS(select.toString());
		} catch (Exception e) {
			log.error("sql解析异常，sql:{}, e:{}", sql, e);
			return ResultMsg.ERROR(ExceptionUtil.getRootErrorMseeage(e));
		}
	}
	
	/**
	 * 获取sql查询的字段别名
	 * @param selectBody
	 * @return
	 * @return Map<String,String>
	 * @author xianggang
	 * @date 2019年9月3日 下午12:08:11
	 */
	public static Map<String, String> handleColumnAliasMap(SelectBody selectBody,
	        Map<String, String> oldColumnAliasMap) {
		PlainSelect plainSelect = (PlainSelect) selectBody;
		List<SelectItem> selectItems = plainSelect.getSelectItems();
		
		Map<String, String> columnAliasMap = new HashMap<String, String>();
		
		for (SelectItem selectItem : selectItems) {
			if (selectItem instanceof AllColumns || selectItem instanceof AllTableColumns) {
				continue;
			}
			SelectExpressionItem item = (SelectExpressionItem) selectItem;
			Alias fieldAlias = item.getAlias();
			String expression = item.getExpression().toString();
			
			String fieldAliasName = "";
			if (fieldAlias != null) {
				fieldAliasName = fieldAlias.getName();
			}
			
			if (oldColumnAliasMap != null) {
				String oldAlias = oldColumnAliasMap.get(expression);
				if (StringUtil.isNotEmpty(oldAlias)) {
					fieldAlias = new Alias(oldAlias);
				} else {
					expression = expression.replaceAll("`", "");
					String[] split = expression.split("\\.");
					String column = "";
					if (split.length == 1) {
						column = split[0];
					} else {
						column = split[1];
					}
					fieldAlias = new Alias(StringUtil.underlineToCamel(column));
				}
				
				item.setAlias(fieldAlias);
			}
			
			columnAliasMap.put(expression, fieldAliasName);
		}
		return columnAliasMap;
	}
	
	/**
	 * 按照数据库类型，封装SQL
	 * 
	 * @param dbType
	 *            数据库类型
	 * @param sql
	 * @param page
	 * @param rows
	 * @return
	 */
	public static String createPageSql(String dbType, String sql, int page, int rows) {
		int beginNum = (page - 1) * rows;
		String[] sqlParam = new String[3];
		sqlParam[0] = sql;
		sqlParam[1] = beginNum + "";
		sqlParam[2] = rows + "";
		String jdbcType = dbType;
		if (jdbcType == null || "".equals(jdbcType)) {
			throw new RuntimeException("org.jeecgframework.minidao.aop.MiniDaoHandler:(数据库类型:dbType)没有设置,请检查配置文件");
		}
		if (jdbcType.indexOf(DATABSE_TYPE_MYSQL) != -1) {
			sql = sql + " limit " + beginNum + "," + rows;
		} else if (jdbcType.indexOf(DATABSE_TYPE_POSTGRE) != -1) {
			sql = MessageFormat.format(POSTGRE_SQL, sqlParam);
		} else {
			int beginIndex = (page - 1) * rows;
			int endIndex = beginIndex + rows;
			sqlParam[2] = Integer.toString(beginIndex);
			sqlParam[1] = Integer.toString(endIndex);
			if (jdbcType.indexOf(DATABSE_TYPE_ORACLE) != -1) {
				sql = MessageFormat.format(ORACLE_SQL, sqlParam);
			} else if (jdbcType.indexOf(DATABSE_TYPE_SQLSERVER) != -1) {
				sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
				sql = MessageFormat.format(SQLSERVER_SQL, sqlParam);
			}
		}
		return sql;
	}
}
